#!/usr/bin/python
#-*-coding: utf-8 -*- 
# Script qui créer une base de données ADPD_Database, avec une table Iris contenant les données du fichier iris.csv, une table Parkinson à partir des données du fichier parkinson.data, une table Wine à partir des données du fichier wine.data , une table Chainlink à partir des données du fichier chainlink.data, une table Hepta à partir du fichier hepta.data
# Le script prend comme argument obligatoire le mot de passe du compte root SQL
# Roxane Levy
import MySQLdb 
import sys



def main() : 

	if (len(sys.argv) < 2) :
		print "usage : %s PASSWORD" %sys.argv[0]
		exit(0)
	# Pour l'accès à la base de donnée
	HOST = 'localhost'
	USER = 'root'
	PASSWD = sys.argv[1]
	DATABASE = 'ADPD_Database'
	# connexion à la BDD
	db_connection = MySQLdb.connect(
       		 host=HOST,
      		 user=USER, 
       		 passwd=PASSWD, 
       		 )

	# il faut un curseur pour exécuter la commande
	cursor = db_connection.cursor()

	cursor.execute("DROP DATABASE IF EXISTS ADPD_Database")
	cursor.execute('CREATE DATABASE ADPD_Database')
	cursor.execute('USE ADPD_Database')


	sql = """
		CREATE TABLE Iris(
 		 id INTEGER  NOT NULL AUTO_INCREMENT,
 		 color1 DOUBLE NOT NULL,
 		 color2 DOUBLE NOT NULL,
 		 color3 DOUBLE NOT NULL,
 		 color4 DOUBLE NOT NULL,
 		 espece VARCHAR(30) NOT NULL,
		 PRIMARY KEY(id) )
		"""
	cursor.execute(sql)



	with open("iris.csv", "r") as iris :
  		lines = iris.readlines()


	for line in lines :
 		 string = line.split()
 		 data = string[0].split(',')
 		 sql = """INSERT INTO Iris (color1,color2,color3,color4,espece) 
			VALUES ('%s','%s','%s','%s','%s')"""%(float(data[0]),float(data[1]),float(data[2]), float(data[3]),str(data[4]))
 		 cursor.execute(sql)





	# Création de la deuxième table pour Parkinson
	sql = """
		CREATE TABLE Parkinson(
 		 id INTEGER  NOT NULL AUTO_INCREMENT,
 		 name VARCHAR(14) NOT NULL,
 		 mdvp_fo DOUBLE NOT NULL,
 		 mdvp_fhi DOUBLE NOT NULL,
 		 mdvp_flo DOUBLE NOT NULL,
 		 jitter DOUBLE NOT NULL,
  		 jitter_abs DOUBLE NOT NULL,
 		 jitter_rap DOUBLE NOT NULL,
 		 jitter_ddp DOUBLE NOT NULL,
 		 jitter_ppq DOUBLE NOT NULL,
  		 shimmer DOUBLE NOT NULL,
  		 shimmer_db DOUBLE NOT NULL,
  		 shimmer_apq3 DOUBLE NOT NULL,
 		 shimmer_apq5 DOUBLE NOT NULL,
 		 shimmer_apq DOUBLE NOT NULL,
 		 shimmer_dda DOUBLE NOT NULL,
  		 nhr DOUBLE NOT NULL,
  		 hnr DOUBLE NOT NULL,
 		 status TINYINT(1) NOT NULL,
 		 rpde DOUBLE NOT NULL,
 		 dfa DOUBLE NOT NULL,
 		 spread1 DOUBLE NOT NULL,
 		 spread2 DOUBLE NOT NULL,
 		 d2 DOUBLE NOT NULL, 
		 ppe DOUBLE NOT NULL,
 		 PRIMARY KEY(id) )
		"""
	 

	cursor.execute(sql)
        with open("parkinsons.data", "r") as parkinson :  lines = parkinson.readlines()
  	for line in lines :
    		string = line.split()
  		data = string[0].split(',')
  		sql = """INSERT INTO Parkinson (name ,mdvp_fo, mdvp_fhi,mdvp_flo,jitter,jitter_abs,jitter_rap,jitter_ddp,jitter_ppq,shimmer,shimmer_db,shimmer_apq3,shimmer_apq5,shimmer_apq,shimmer_dda,nhr,
  hnr,status,rpde,dfa,spread1 ,spread2,d2, ppe) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s')"""%(data[0],float(data[1]),float(data[2]),float(data[3]),float(data[4]),float(data[5]),float(data[6]),float(data[7]),float(data[8]),float(data[9]),float(data[10]),float(data[11]),float(data[12]),float(data[13]),float(data[14]),float(data[15]),float(data[16]),int(data[17]),float(data[18]),float(data[19]),float(data[20]),float(data[21]),float(data[22]),float(data[23]))
  		cursor.execute(sql)





	sql = """
		CREATE TABLE Wine(
 		 id INTEGER  NOT NULL AUTO_INCREMENT,
 		 alcohol INT NOT NULL,
 		 malic_acid DOUBLE NOT NULL,
 		 ash DOUBLE NOT NULL,
 		 alcalinity_ash DOUBLE NOT NULL,
		 magnesium DOUBLE NOT NULL,
		 total_phenols DOUBLE NOT NULL,
 		 flavanoids DOUBLE NOT NULL,
		 nonflavanoid_phenols DOUBLE NOT NULL,
		 proanthocyanins DOUBLE NOT NULL,
		 color_intensity DOUBLE NOT NULL,
		 hue DOUBLE NOT NULL,
		 diluted_wines DOUBLE NOT NULL,
		 proline DOUBLE NOT NULL,
		 PRIMARY KEY(id) )
		"""
	cursor.execute(sql)



	with open("wine.data", "r") as  wine :
  		lines = wine.readlines()


	for line in lines :
 		 string = line.split()
 		 data = string[0].split(',')
 		 sql = """INSERT INTO Wine ( alcohol, malic_acid, ash, alcalinity_ash,	magnesium ,total_phenols,flavanoids,nonflavanoid_phenols,proanthocyanins,color_intensity,	hue,diluted_wines,proline) 
			VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"""%(float(data[0]),float(data[1]),float(data[2]), float(data[3]),float(data[4]),float(data[5]),float(data[6]),float(data[7]),float(data[8]),float(data[9]),float(data[10]),float(data[11]),float(data[12]))
 		 cursor.execute(sql)

	  


	sql = """
		CREATE TABLE Chainlink(
 		 id INTEGER  NOT NULL AUTO_INCREMENT,
 		 coord1 DOUBLE NOT NULL,
 		 coord2 DOUBLE NOT NULL,
 		 coord3 DOUBLE NOT NULL,
		 PRIMARY KEY(id) )
		"""
	cursor.execute(sql)



	with open("chainlink.data", "r") as  chainlink :
  		lines = chainlink.readlines()


	for line in lines :
 		 string = line.split()
 		 data = string[0].split(',')
 		 sql = """INSERT INTO Chainlink (coord1,coord2,coord3) 
			VALUES ('%s','%s','%s')"""%(float(data[0]),float(data[1]),float(data[2]))
 		 cursor.execute(sql)





	sql = """
		CREATE TABLE Hepta(
 		 id INTEGER  NOT NULL AUTO_INCREMENT,
 		 coord1 DOUBLE NOT NULL,
 		 coord2 DOUBLE NOT NULL,
 		 coord3 DOUBLE NOT NULL,
		 PRIMARY KEY(id) )
		"""
	cursor.execute(sql)



	with open("hepta.data", "r") as  hepta :
  		lines = hepta.readlines()


	for line in lines :
 		 string = line.split()
 		 data = string[0].split(',')
 		 sql = """INSERT INTO Hepta (coord1,coord2,coord3) 
			VALUES ('%s','%s','%s')"""%(float(data[0]),float(data[1]),float(data[2]))
 		 cursor.execute(sql)




	try:
  		 db_connection.commit()
	except:
  		 db_connection.rollback()

	db_connection.close()



if __name__ == "__main__":
	main()

